﻿#include "databaseManager.h"

// Class construction/deconstruction and singleton pattern
DatabaseManager& DatabaseManager::instance() {
    static DatabaseManager instance;
    return instance;
}

QSqlDatabase DatabaseManager::getDatabase() {
    return db;
}

DatabaseManager::DatabaseManager() {
    QString dbType;
    if (loadDatabaseConfig(dbType)) {
        if (dbType == "QSQLITE") {
            QString dbName;
            loadSqliteConfig(dbName);
            connectToSqlite(dbName);
        } else if (dbType == "QMYSQL") {
            QString host, dbName, user, password;
            int port;
            loadMySQLConfig(host, port, dbName, user, password);
            connectToMySQL(host, port, dbName, user, password);
        }
    }
}

DatabaseManager::DatabaseManager(const QString& dbName) {
    connectToSqlite(dbName);
}

DatabaseManager::~DatabaseManager() {
    closePrevConnection();
}



// Database connection and initialization
bool DatabaseManager::connectToSqlite(const QString& dbName) {
    closePrevConnection();
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbName);

    if (!db.open()) {
        return false;
    } else {
        initializeSqliteDatabase();
        return true;
    }
}

bool DatabaseManager::connectToMySQL(const QString& hostName, int port, const QString& dbName, const QString& username, const QString& password) {
    closePrevConnection();
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(hostName);
    db.setPort(port);
    db.setDatabaseName(dbName);
    db.setUserName(username);
    db.setPassword(password);

    if (!db.open()) {
        return false;
    } else {
        initializeMySQLDatabase();
        return true;
    }
}

void DatabaseManager::closePrevConnection() {
    if (db.isOpen()) {
        db.close();
    }
}

void DatabaseManager::initializeMySQLDatabase() {
    QSqlQuery query(db);

    // Create Authentication table
    if (!query.exec("CREATE TABLE IF NOT EXISTS Authentication ("
                    "id INT PRIMARY KEY AUTO_INCREMENT, "
                    "username VARCHAR(255), "
                    "password VARCHAR(255), "
                    "position VARCHAR(255))")) {
       // //qDEbug() << "Failed to create Authentication table:" << query.lastError().text();
        return;
    }

    // Insert initial record into Authentication table if it doesn't exist
    if (!query.exec("INSERT IGNORE INTO Authentication (id, username, password, position) VALUES (1, 'admin', 'admin', '管理员')")) {
       // //qDEbug() << "Failed to insert initial record into Authentication table:" << query.lastError().text();
        return;
    }

    // Create PurchaseOrder table
    if (!query.exec("CREATE TABLE IF NOT EXISTS PurchaseOrder ("
                    "id INT PRIMARY KEY AUTO_INCREMENT, "
                    "orderMaterialType VARCHAR(255), "
                    "amount INT, "
                    "acceptDate VARCHAR(255), "
                    "orderStatus VARCHAR(255), "
                    "prepaid INT, "
                    "clientName VARCHAR(255), "
                    "clientTEL VARCHAR(255))")) {
        //qDEbug() << "Failed to create PurchaseOrder table:" << query.lastError().text();
        return;
    }

    // Create Finance table
    if (!query.exec("CREATE TABLE IF NOT EXISTS Finance ("
                    "id INT PRIMARY KEY AUTO_INCREMENT, "
                    "matchingId INT, "
                    "recordType VARCHAR(255), "
                    "recordDate VARCHAR(255), "
                    "amount INT, "
                    "totalIncome INT, "
                    "totalOutcome INT,"
                    "totalInterest INT)")) {
        //qDEbug() << "Failed to create Finance table:" << query.lastError().text();
        return;
    }

    // Create EnterRecord table
    if (!query.exec("CREATE TABLE IF NOT EXISTS EnterRecord ("
                    "id INT PRIMARY KEY AUTO_INCREMENT, "
                    "EnterDate VARCHAR(255), "
                    "EnterMaterialType VARCHAR(255), "
                    "EnterAmount INT, "
                    "unitPrice INT, "
                    "totalPrice INT)")) {
        //qDEbug() << "Failed to create EnterRecord table:" << query.lastError().text();
        return;
    }

    // Create Inventory table
    if (!query.exec("CREATE TABLE IF NOT EXISTS Inventory ("
                    "id INT PRIMARY KEY AUTO_INCREMENT, "
                    "rawMaterialType VARCHAR(255), "
                    "amount INT)")) {
        //qDEbug() << "Failed to create Inventory table:" << query.lastError().text();
        return;
    }

    //qDEbug() << "Tables created successfully!";
}

void DatabaseManager::initializeSqliteDatabase() {
    QSqlQuery query(db);

    // Create Authentication table

    if (!query.exec("CREATE TABLE IF NOT EXISTS Authentication ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "username TEXT, "
                    "password TEXT, "
                    "position TEXT)")) {
        //qDEbug() << "Failed to create Authentication table:" << query.lastError().text();
        return;
    }

    // Insert initial record into Authentication table if it doesn't exist

    if (!query.exec("INSERT INTO Authentication (username, password, position) "
                    "SELECT 'admin', 'admin', '管理员' "
                    "WHERE NOT EXISTS (SELECT 1 FROM Authentication WHERE id = 1)")) {
        //qDEbug() << "Failed to insert initial record into Authentication table:" << query.lastError().text();
        return;
    }

    // Create PurchaseOrder table

    if (!query.exec("CREATE TABLE IF NOT EXISTS PurchaseOrder ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "orderMaterialType TEXT, "
                    "amount INT, "
                    "acceptDate TEXT, "
                    "orderStatus TEXT, "
                    "prepaid INT, "
                    "clientName TEXT, "
                    "clientTEL TEXT)")) {
        //qDEbug() << "Failed to create PurchaseOrder table:" << query.lastError().text();
        return;
    }

    // Create Finance table

    if (!query.exec("CREATE TABLE IF NOT EXISTS Finance ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "matchingId INT, "
                    "recordType TEXT, "
                    "recordDate TEXT, "
                    "amount INT, "
                    "totalIncome INT, "
                    "totalOutcome INT,"
                    "totalInterest INT)")) {
        //qDEbug() << "Failed to create Finance table:" << query.lastError().text();
        return;
    }

    // Create EnterRecord table

    if (!query.exec("CREATE TABLE IF NOT EXISTS EnterRecord ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "EnterDate TEXT, "
                    "EnterMaterialType TEXT, "
                    "EnterAmount INT, "
                    "unitPrice INT, "
                    "totalPrice INT)")) {
        //qDEbug() << "Failed to create EnterRecord table:" << query.lastError().text();
        return;
    }

    // Create Inventory table

    if (!query.exec("CREATE TABLE IF NOT EXISTS Inventory ("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "rawMaterialType TEXT, "
                    "amount INT)")) {
        //qDEbug() << "Failed to create Inventory table:" << query.lastError().text();
        return;
    }

    //qDEbug() << "Tables created successfully!";
}

/**
 * @brief Init db connect before login auto base on the config file. If fails, then user need to connect by hand
 * @return false if auto connect fails, ture if succeed
 */
bool DatabaseManager::initDatabaseConnection() {
    if (db.isOpen()) {
        return true;
    }

    QString dbType;
    if (!loadDatabaseConfig(dbType)) {
        return false;
    }

    if (dbType == "QSQLITE") {
        QString dbName;
        loadSqliteConfig(dbName);
        connectToSqlite(dbName);
    } else if (dbType == "QMYSQL") {
        QString host, dbName, user, password;
        int port;
        loadMySQLConfig(host, port, dbName, user, password);
        connectToMySQL(host, port, dbName, user, password);
    }

    return db.isOpen();
}



// Database configuration SL
void DatabaseManager::saveDatabaseConfig(const QString& dbType, const QString& host, int port, const QString& dbName, const QString& user, const QString& password) {
    QSettings settings("dbconfig.ini", QSettings::IniFormat);
    settings.setValue("database/type", dbType);
    settings.setValue("database/host", host);
    settings.setValue("database/port", port);
    settings.setValue("database/dbName", dbName);
    settings.setValue("database/user", user);
    settings.setValue("database/password", password);
}

void DatabaseManager::saveSqliteConfig(const QString& dbName) {
    QSettings settings("dbconfig.ini", QSettings::IniFormat);
    settings.setValue("database/type", "QSQLITE");
    settings.setValue("database/dbName", dbName);
}

bool DatabaseManager::loadDatabaseConfig(QString& dbType) {
    QSettings settings("dbconfig.ini", QSettings::IniFormat);
    dbType = settings.value("database/type").toString();
    if (dbType.isEmpty()) {
        //qDEbug() << "Database type not found in config file.";
        return false;
    } else {
        //qDEbug() << "Database type:" << dbType;
        return true;
    }
}

bool DatabaseManager::loadSqliteConfig(QString& dbName) {
    QSettings settings("dbconfig.ini", QSettings::IniFormat);
    dbName = settings.value("database/dbName").toString();
    if (dbName.isEmpty()) {
        //qDEbug() << "SQLite database name not found in config file.";
        return false;
    } else {
        //qDEbug() << "SQLite database name:" << dbName;
        return true;
    }
}

bool DatabaseManager::loadMySQLConfig(QString& host, int& port, QString& dbName, QString& user, QString& password) {
    QSettings settings("dbconfig.ini", QSettings::IniFormat);
    host = settings.value("database/host").toString();
    port = settings.value("database/port").toInt();
    dbName = settings.value("database/dbName").toString();
    user = settings.value("database/user").toString();
    password = settings.value("database/password").toString();

    if (host.isEmpty() || dbName.isEmpty() || user.isEmpty() || password.isEmpty()) {
        //qDEbug() << "MySQL config not found in config file.";
        return false;
    } else {
        //qDEbug() << "MySQL host:" << host << ", port:" << port << ", dbName:" << dbName << ", user:" << user << ", password:" << password;
        return true;
    }
}

/**
 * @brief Get the row counts of all tables
 * @return a list of length 5 that contains the row counts of all tables
 */
QList<int> DatabaseManager::getTableRowCounts() {
    QList<int> rowCounts;
    QSqlQuery query(db);

    QStringList tables = { "Authentication", "PurchaseOrder", "Finance", "EnterRecord", "Inventory" };

    for (const QString& table : tables) {
        if (query.exec(QString("SELECT COUNT(*) FROM %1").arg(table))) {
            if (query.next()) {
                rowCounts.append(query.value(0).toInt());
            } else {
                //qDEbug() << "Failed to retrieve row count for table" << table << ":" << query.lastError().text();
                rowCounts.append(-1); // Indicate an error with -1
            }
        } else {
            //qDEbug() << "Failed to execute query for table" << table << ":" << query.lastError().text();
            rowCounts.append(-1); // Indicate an error with -1
        }
    }

    return rowCounts;
}

// Operations for Authentication talbe
/**
 * @brief Inserts a new authentication record into the Authentication table. Repeated username is banned.
 * @param username The username for the new authentication record.
 * @param password The password for the new authentication record.
 * @param position The position for the new authentication record.
 * @return true if the insertion was successful, false otherwise.
 */
bool DatabaseManager::insertAuthentication(const QString& username, const QString& password, const QString& position) {
    // Check if the username already exists
    QSqlQuery checkQuery(db);
    checkQuery.prepare("SELECT username FROM Authentication WHERE username = :username");
    checkQuery.bindValue(":username", username);

    if (checkQuery.exec() && checkQuery.next()) {
        //qDEbug() << "Error: Username" << username << "already exists in Authentication table.";
        return false; // Username already exists, insertion failed
    }

    // insert into Authenciation table
    QSqlQuery insertQuery(db);
    QString sql = QString("INSERT INTO Authentication (username, password, position) VALUES ('%1', '%2', '%3')")
                    .arg(username)
                    .arg(password)
                    .arg(position);

    if (!insertQuery.exec(sql)) {
        //qDEbug() << "Error: Failed to insert into Authentication table:"
                // << insertQuery.lastError().text();
        return false;
    }

    return true;
}

/**
 * @brief Check if a record is in Authentication table
 * @param username The username of the record to check
 * @param password The password of the record to check
 * @param position The position of the position to check
 * @return true if all three info of the record is matching, false otherwise
 */
bool DatabaseManager::checkAuthentication(const QString& username, const QString& password, const QString& position) {
    QSqlQuery query(db);
    query.prepare("SELECT * FROM Authentication WHERE username = :username AND password = :password AND position = :position");
    query.bindValue(":username", username);
    query.bindValue(":password", password);
    query.bindValue(":position", position);

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to select from Authentication table:"
               //  << query.lastError().text();
        //qDEbug() << "username: " << username;
        //qDEbug() << "password: " << password;
        //qDEbug() << "position: " << position;
        return false; // query fails, auth fails
    }

    return query.next(); // true if there is a matching record, false otherwise
}

/**
 * @brief update given id record in Authentication table. Invalid id or repeated username is prohibited
 * @param id The id of the record to update
 * @param username The new username for updating
 * @param password The new password for updating
 * @param position The new position for updating
 * @return true if updating succeed, false otherwise
 */
bool DatabaseManager::updateAuthentication(int id, const QString& username, const QString& password, const QString& position) {
    // Check if the record with the given id exists
    QSqlQuery checkIdQuery(db);
    checkIdQuery.prepare("SELECT id FROM Authentication WHERE id = :id");
    checkIdQuery.bindValue(":id", id);

    if (!checkIdQuery.exec()) {
        //qDEbug() << "Error: Failed to check for existing id in Authentication table:"
                // << checkIdQuery.lastError().text();
        return false;
    }

    if (!checkIdQuery.next()) {
        //qDEbug() << "Error: Record with id" << id << "does not exist in Authentication table.";
        return false; // Record with the given id does not exist, update failed
    }

    // Update the record
    QSqlQuery updateQuery(db);
    updateQuery.prepare("UPDATE Authentication SET username = :username, password = :password, position = :position "
                        "WHERE id = :id");
    updateQuery.bindValue(":username", username);
    updateQuery.bindValue(":password", password);
    updateQuery.bindValue(":position", position);
    updateQuery.bindValue(":id", id);

    if (!updateQuery.exec()) {
      //  //qDEbug() << "Error: Failed to update Authentication table:"
                // << updateQuery.lastError().text();
        return false;
    }

    return true;
}

/**
 * @brief Delete the record with the given id in Authentication. Not recommended to use, just implement here for testing
 * @param id The id of the record to be deleted
 * @return true if deletion succeed, false otherwise
 */
bool DatabaseManager::deleteAuthentication(int id) {
    // Check if the record with the given id exists
    QSqlQuery checkIdQuery(db);
    checkIdQuery.prepare("SELECT id FROM Authentication WHERE id = :id");
    checkIdQuery.bindValue(":id", id);

    if (!checkIdQuery.exec()) {
       // //qDEbug() << "Error: Failed to check for existing id in Authentication table:"
                // << checkIdQuery.lastError().text();
        return false;
    }

    if (!checkIdQuery.next()) {
        //qDEbug() << "Error: Record with id" << id << "does not exist in Authentication table.";
        return false; // Record with the given id does not exist, deletion failed
    }

    // Delete the record
    QSqlQuery deleteQuery(db);
    deleteQuery.prepare("DELETE FROM Authentication WHERE id = :id");
    deleteQuery.bindValue(":id", id);

    if (!deleteQuery.exec()) {
        ////qDEbug() << "Error: Failed to delete from Authentication table:"
                //<< deleteQuery.lastError().text();
        return false;
    }

    return true;
}

QSqlTableModel* DatabaseManager::getAuthenticationModel() {
    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("Authentication");
    model->select();
    return model;
}

void DatabaseManager::cleanAuthentication() {
    QSqlQuery query;
    query.prepare("DELETE FROM Authentication");

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to clear Authentication table:" << query.lastError().text();
    }
}



// Operations for PurchaseOrder table
/**
 * @brief Insert purchase order record into PurchaseOrder table, once a row
 * @param orderMaterialType Type of the material that this order demands
 * @param amount The amount of product that this order needs
 * @param acceptDate The date that this order is recorded
 * @param orderStatus The status of this order, either 未完成 or 已完成
 * @param prepaid The prepaid for this order
 * @param clientName The name of the client of this order that we connect with
 * @param clientTEL The TEL of the client of this order that we connect with
 * @return return true if insertion is successful, false otherwise
 */
bool DatabaseManager::insertPurchaseOrder(const QString& orderMaterialType, int amount, const QString& acceptDate, const QString& orderStatus, int prepaid, const QString& clientName, const QString& clientTEL) {
    QSqlQuery query(db);

    // Construct the query string with parameters directly
    QString queryString = QString("INSERT INTO PurchaseOrder (orderMaterialType, amount, acceptDate, orderStatus, prepaid, clientName, clientTEL) "
                                  "VALUES ('%1', %2, '%3', '%4', %5, '%6', '%7')")
                          .arg(orderMaterialType)
                          .arg(amount)
                          .arg(acceptDate)
                          .arg(orderStatus)
                          .arg(prepaid)
                          .arg(clientName)
                          .arg(clientTEL);

    if (!query.exec(queryString)) {
        //qDEbug() << "Error: Failed to insert into PurchaseOrder table:" << query.lastError().text();
//        //qDEbug() << "Query:" << queryString;
        return false;
    }
    return true;
}

/**
 * @brief Return the QSqlTableModel for frontend to show the purchase order table
 * @return The QSqlTableModel object pointer
 */
QSqlTableModel* DatabaseManager::getPurchaseOrderModel() {
    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("PurchaseOrder");
    model->select();

    // 设置各列的标签
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Order Material Type"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("Amount"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("Accept Date"));
    model->setHeaderData(4, Qt::Horizontal, QObject::tr("Condition"));
    model->setHeaderData(5, Qt::Horizontal, QObject::tr("Prepaid"));
    model->setHeaderData(6, Qt::Horizontal, QObject::tr("Client Name"));
    model->setHeaderData(7, Qt::Horizontal, QObject::tr("Client TEL"));

    return model;
}

/**
 * @brief Update the order status to be completed("已完成"). Should be used when final payment is inserted into Finance table
 * @param id The id of the order whose status should be changed
 * @return true if updating succeed, false otherwise
 */
bool DatabaseManager::markPurchaseOrderAsCompleted(int id) {
    QSqlQuery checkQuery(db);
    checkQuery.prepare("SELECT orderStatus FROM PurchaseOrder WHERE id = :id");
    checkQuery.bindValue(":id", id);

    if (!checkQuery.exec() || !checkQuery.next()) {
        //qDEbug() << "Error: Order ID" << id << "does not exist in PurchaseOrder table.";
        return false; // Order ID does not exist, cannot mark as completed
    }

    QString orderStatus = checkQuery.value(0).toString();
    if (orderStatus == "已完成") {
        //qDEbug() << "Order ID" << id << "is already marked as completed.";
        return false; // Order is already completed, cannot mark as completed again
    }

    QSqlQuery updateQuery(db);
    updateQuery.prepare("UPDATE PurchaseOrder SET orderStatus = '已完成' WHERE id = :id");
    updateQuery.bindValue(":id", id);

    if (!updateQuery.exec()) {
        //qDEbug() << "Error: Failed to mark order ID" << id << "as completed:"
                // << updateQuery.lastError().text();
        return false;
    }

    return true;
}

/**
 * @brief Get specific purchase order record by id, with appointed more readable column names
 * @param id The id to specify to purchase order record
 * @return QSqlTableModel pointer containing the row if given id exists, nullptr otherwise
 */
QSqlTableModel* DatabaseManager::getPurchaseOrderById(int id) {
    QSqlQuery checkQuery(db);
    checkQuery.prepare("SELECT COUNT(*) FROM PurchaseOrder WHERE id = :id");
    checkQuery.bindValue(":id", id);

    if (!checkQuery.exec() || !checkQuery.next() || checkQuery.value(0).toInt() == 0) {
        //qDEbug() << "Error: PurchaseOrder with id" << id << "does not exist.";
        return nullptr;
    }

    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("PurchaseOrder");
    model->setFilter(QString("id = %1").arg(id));
    model->select();

    // Set header data (column names)
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Order Material Type"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("Amount"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("Accept Date"));
    model->setHeaderData(4, Qt::Horizontal, QObject::tr("Condition"));
    model->setHeaderData(5, Qt::Horizontal, QObject::tr("Prepaid"));
    model->setHeaderData(6, Qt::Horizontal, QObject::tr("Client Name"));
    model->setHeaderData(7, Qt::Horizontal, QObject::tr("Client TEL"));

    return model;
}

QList<QPair<QString, QString>> DatabaseManager::getPurchaseOrderDateAndCondition() {
    QList<QPair<QString, QString>> result;

    QSqlQuery query;
    query.prepare("SELECT acceptDate, orderStatus FROM PurchaseOrder");

    if (query.exec()) {
        while (query.next()) {
            QString purchaseOrderDate = query.value(0).toString();
            QString orderStatus = query.value(1).toString();
            result.append(QPair<QString, QString>(purchaseOrderDate, orderStatus));
        }
    } else {
        //qDEbug() << "Failed to execute query:" << query.lastError().text();
    }

    return result;
}

/**
 * @brief 用于财政的matchingId，实际返回了将要插入的记录的id
 * @return The id of this record (which is being inserted)
 */
int DatabaseManager::getLastPurchaseOrderId() {
    int lastId = 0;

    QSqlQuery query(db);
    query.prepare("SELECT id FROM PurchaseOrder ORDER BY id DESC LIMIT 1");

    if (query.exec() && query.first()) {
        lastId = query.value(0).toInt();
    } else {
        //qDEbug() << "Error: Failed to retrieve last purchase order ID:" << query.lastError().text();
    }

    return lastId + 1;
}

void DatabaseManager::cleanPurchaseOrder() {
    QSqlQuery query;
    query.prepare("DELETE FROM PurchaseOrder");

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to clear PurchaseOrder table:" << query.lastError().text();
    }
}



// Operations for Finance table
/**
 * @brief Insert finance record into table
 * @param matchingId The id of purchase order or enter repo record that this finance record corresponds to
 * @param recordType The type of record, has 支出，首款，尾款 three types
 * @param recordDate The date of this record
 * @param totalIncome The total income from starting
 * @param totalOutcome The total outcome from starting
 * @param totalInterest The total interest from starting
 * @return true if the record is inserted successfully, false otherwise
 */
bool DatabaseManager::insertFinanceRecord(int matchingId, const QString& recordType, const QString& recordDate, int amount, int totalIncome, int totalOutcome, int totalInterest) {
    // Check if matchingId exists in the corresponding table based on recordType
    QSqlQuery checkQuery(db);

    if (recordType == "支出") {
        // Check in EnterRecord table
        checkQuery.prepare("SELECT id FROM EnterRecord WHERE id = :id");
    } else if (recordType == "首款" || recordType == "尾款"){
        // Check in PurchaseOrder table for other record types
        checkQuery.prepare("SELECT id FROM PurchaseOrder WHERE id = :id");
    } else {
        //qDEbug() << "The recordType is neither 支出, 尾款 or 首款";
    }

    checkQuery.bindValue(":id", matchingId);
    if (!checkQuery.exec() || !checkQuery.next()) {
        //qDEbug() << "Error: matchingId" << matchingId << "does not exist in the corresponding table for recordType" << recordType;
        return false; // matchingId does not exist
    }

    // Proceed with the insertion
    QSqlQuery query(db);
    query.prepare("INSERT INTO Finance (matchingId, recordType, recordDate, amount, totalIncome, totalOutcome, totalInterest) "
                  "VALUES (:matchingId, :recordType, :recordDate, :amount, :totalIncome, :totalOutcome, :totalInterest)");
    query.bindValue(":matchingId", matchingId);
    query.bindValue(":recordType", recordType);
    query.bindValue(":recordDate", recordDate);
    query.bindValue(":amount", amount);
    query.bindValue(":totalIncome", totalIncome);
    query.bindValue(":totalOutcome", totalOutcome);
    query.bindValue(":totalInterest", totalInterest);

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to insert into Finance table:" << query.lastError().text();
        return false;
    }

    return true;
}

/**
 * @brief Get the model for showing the whole Finance table
 * @return QSqlTableModel* for displaying Finance table
 */
QSqlTableModel* DatabaseManager::getFinanceTableModel() {
    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("Finance");
    model->select();

    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "Matching ID");
    model->setHeaderData(2, Qt::Horizontal, "Record Type");
    model->setHeaderData(3, Qt::Horizontal, "Record Date");
    model->setHeaderData(4, Qt::Horizontal, "Amount");
    model->setHeaderData(5, Qt::Horizontal, "Total Income");
    model->setHeaderData(6, Qt::Horizontal, "Total Outcome");
    model->setHeaderData(7, Qt::Horizontal, "Total Interest");

    return model;
}

/**
 * @brief Get finance record by id
 * @param id The id to specify the row of finance record
 * @return QSqlTableModel* for show the row of finance record
 */
QSqlTableModel* DatabaseManager::getFinanceRecordById(int id) {
    QSqlQuery checkQuery(db);
    checkQuery.prepare("SELECT COUNT(*) FROM Finance WHERE id = :id");
    checkQuery.bindValue(":id", id);

    if (!checkQuery.exec()) {
        //qDEbug() << "Error: Failed to execute query:" << checkQuery.lastError().text();
        return nullptr;
    }

    if (!checkQuery.next() || checkQuery.value(0).toInt() == 0) {
        //qDEbug() << "Error: Finance record with id" << id << "does not exist.";
        return nullptr;
    }

    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("Finance");
    model->setFilter(QString("id = %1").arg(id));
    model->select();

    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "Matching ID");
    model->setHeaderData(2, Qt::Horizontal, "Record Type");
    model->setHeaderData(3, Qt::Horizontal, "Record Date");
    model->setHeaderData(4, Qt::Horizontal, "Amount");
    model->setHeaderData(5, Qt::Horizontal, "Total Income");
    model->setHeaderData(6, Qt::Horizontal, "Total Outcome");
    model->setHeaderData(7, Qt::Horizontal, "Total Interest");

    return model;
}

/**
 * @brief Get the three total finance records from the last row. If no row exist, return three 0 as beginning
 * @return  QList<int> that contains three total finance records
 */
QList<int> DatabaseManager::getFinanceLastTotal() {
    QList<int> result;

    QSqlQuery query;
    query.prepare("SELECT TotalIncome, TotalOutcome, TotalInterest FROM Finance ORDER BY id DESC LIMIT 1");

    if (query.exec() && query.first()) {
        result.push_back(query.value(0).toInt()); // TotalIncome
        result.push_back(query.value(1).toInt()); // TotalOutcome
        result.push_back(query.value(2).toInt()); // TotalInterest
    } else {
        //qDEbug() << "Failed to execute query or no rows found:" << query.lastError().text();
    }

    while (result.size() < 3) {
        result.push_back(0);
    }

    return result;
}

QList<QList<QVariant>> DatabaseManager::getFinanceRecordsForLinePlot() {
    QList<QList<QVariant>> records;

    QSqlQuery query;
    query.prepare("SELECT recordType, recordDate, amount FROM Finance");

    if (query.exec()) {
        while (query.next()) {
            QList<QVariant> record;
            record.append(query.value(0)); // recordType
            record.append(query.value(1)); // recordDate
            record.append(query.value(2)); // amount
            records.append(record);
        }
    } else {
        //qDEbug() << "Error: Failed to retrieve finance records:" << query.lastError().text();
    }

    return records;
}

void DatabaseManager::cleanFinance() {
    QSqlQuery query;
    query.prepare("DELETE FROM Finance");

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to clear Finance table:" << query.lastError().text();
    }
}



// Operations for EnterRecord table
/**
 * @brief Insert a entering record
 * @param enterDate The date of entering, with format "YYYY-MM-DD"
 * @param enterMaterialType The type of material to enter
 * @param enterAmount The amount of material to enter
 * @param unitPrice The unit price of the material
 * @param totalPrice The total price of the material (this entering)
 * @return true if insertion completes, false otherwise
 */
bool DatabaseManager::insertEnterRecord(const QString& enterDate, const QString& enterMaterialType, int enterAmount, int unitPrice, int totalPrice) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO EnterRecord (EnterDate, EnterMaterialType, EnterAmount, unitPrice, totalPrice) "
                  "VALUES (:enterDate, :enterMaterialType, :enterAmount, :unitPrice, :totalPrice)");
    query.bindValue(":enterDate", enterDate);
    query.bindValue(":enterMaterialType", enterMaterialType);
    query.bindValue(":enterAmount", enterAmount);
    query.bindValue(":unitPrice", unitPrice);
    query.bindValue(":totalPrice", totalPrice);

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to insert into EnterRecord table:"
                // << query.lastError().text();
        return false;
    }

    return true;
}

/**
 * @brief DatabaseManager::getEnterRecordModel
 * @return QSqlTableModel* for showing the whole EnterRecord table
 */
QSqlTableModel* DatabaseManager::getEnterRecordModel() {
    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("EnterRecord");
    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "Enter Date");
    model->setHeaderData(2, Qt::Horizontal, "Material Type");
    model->setHeaderData(3, Qt::Horizontal, "Enter Amount");
    model->setHeaderData(4, Qt::Horizontal, "Unit Price");
    model->setHeaderData(5, Qt::Horizontal, "Total Price");
    model->select();
    return model;
}

/**
 * @brief DatabaseManager::getEnterRecordById
 * @param id The id to query the entering record
 * @return QSqlTableModel* object that contains the row of data that id specified
 */
QSqlTableModel* DatabaseManager::getEnterRecordById(int id) {
    QSqlQuery checkQuery(db);
    checkQuery.prepare("SELECT COUNT(*) FROM EnterRecord WHERE id = :id");
    checkQuery.bindValue(":id", id);

    if (!checkQuery.exec() || !checkQuery.next() || checkQuery.value(0).toInt() == 0) {
        //qDEbug() << "Error: EnterRecord with id" << id << "does not exist.";
        return nullptr;
    }

    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("EnterRecord");
    model->setFilter(QString("id = %1").arg(id));
    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "Enter Date");
    model->setHeaderData(2, Qt::Horizontal, "Material Type");
    model->setHeaderData(3, Qt::Horizontal, "Enter Amount");
    model->setHeaderData(4, Qt::Horizontal, "Unit Price");
    model->setHeaderData(5, Qt::Horizontal, "Total Price");
    model->select();
    return model;
}

/**
 * @brief 用于财政的matchingId，实际上直接返回了这次插入应有的id值
 * @return id
 */
int DatabaseManager::getLastEnterRecordId() {
    int lastId = 0;

    QSqlQuery query(db);
    query.prepare("SELECT id FROM EnterRecord ORDER BY id DESC LIMIT 1");

    if (query.exec() && query.first()) {
        lastId = query.value(0).toInt();
    } else {
        //qDEbug() << "Error: Failed to retrieve last purchase order ID:" << query.lastError().text();
    }

    return lastId + 1;
}



// Operations for Inventory table
/**
 * @brief Change the inventory of type of raw material. Insert if not exist, update if exist
 * @param rawMaterialType Type of the raw material
 * @param amount The amount to enter or fetch
 * @return true if change succeed, false if this fetch will make inventory neg, or just operation fails
 */
bool DatabaseManager::changeInventory(const QString& rawMaterialType, int amount) {
    QSqlQuery query(db);

    // Check if the raw material type exists in the Inventory table
    query.prepare("SELECT amount FROM Inventory WHERE rawMaterialType = :rawMaterialType");
    query.bindValue(":rawMaterialType", rawMaterialType);

    if (!query.exec()) {
        //qDEbug() << "Error: Failed to execute SELECT query:" << query.lastError().text();
        return false;
    }

    if (!query.first()) {
        // If no record exists, insert a new record
        query.prepare("INSERT INTO Inventory (rawMaterialType, amount) "
                      "VALUES (:rawMaterialType, :amount)");
        query.bindValue(":rawMaterialType", rawMaterialType);
        query.bindValue(":amount", amount);

        if (!query.exec()) {
            //qDEbug() << "Error: Failed to insert into Inventory table:" << query.lastError().text();
            return false;
        }
    } else {
        // If record exists, update the existing record
        int currentAmount = query.value(0).toInt();
        int newAmount = currentAmount + amount;

        if (newAmount < 0) {
            //qDEbug() << "Error: Insufficient inventory for deduction.";
            return false;
        }

        query.prepare("UPDATE Inventory SET amount = :newAmount WHERE rawMaterialType = :rawMaterialType");
        query.bindValue(":newAmount", newAmount);
        query.bindValue(":rawMaterialType", rawMaterialType);

        if (!query.exec()) {
            //qDEbug() << "Error: Failed to update Inventory table:" << query.lastError().text();
            return false;
        }
    }

    return true;
}

QSqlTableModel* DatabaseManager::getInventoryModel() {
    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("Inventory");
    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "Raw Material Type");
    model->setHeaderData(2, Qt::Horizontal, "Amount");
    model->select();
    return model;
}

/**
 * @brief DatabaseManager::getInventoryForPiePlot
 * @return list of pair of rawMaterialType and amount
 */
QList<QPair<QString, int>> DatabaseManager::getInventoryForPiePlot() {
    QList<QPair<QString, int>> inventoryList;
    QSqlQuery query(db);

    if (query.exec("SELECT rawMaterialType, amount FROM Inventory")) {
        while (query.next()) {
            QString rawMaterialType = query.value(0).toString();
            int amount = query.value(1).toInt();
            inventoryList.append(QPair<QString, int>(rawMaterialType, amount));
        }
    } else {
        //qDEbug() << "Error: Failed to retrieve data from Inventory table:" << query.lastError().text();
    }

    return inventoryList;
}

/**
 * @brief DatabaseManager::getInventoryByType
 * @param rawMaterialType The type of raw material used to search
 * @return QSqlTableModel* object to show the row
 */
QSqlTableModel* DatabaseManager::getInventoryByType(const QString& rawMaterialType) {
    QSqlTableModel* model = new QSqlTableModel();
    model->setTable("Inventory");
    model->setFilter(QString("rawMaterialType = '%1'").arg(rawMaterialType));
    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "Raw Material Type");
    model->setHeaderData(2, Qt::Horizontal, "Amount");
    model->select();
    return model;
}
